﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace VWise_DAO
{
    public class CPersonalManager
    {
        public const int W_OK = 0;
        public const int W_FAIL = -1;

        public const int W_MARK_INGRESO = 1;
        public const int W_MARK_SALIDA = 2;
        public const int W_MARK_EXCEPCIONAL_INGRESO = 3;
        public const int W_MARK_EXCEPCIONAL_SALIDA = 4;



        //cadena de conexion
        public string szConnectionString = "";

        //informacion de los sp's a ejecutar METODO GetPersonalByID
        public string szGetInfPersonalSP = "SP_GET_INF_PERSONAL";
        public string szParamGetInfPersonalSP = "@N_COD_PERSONA";

        //informacion de los sp's a ejecutar METODO SearchMarks
        public string szGetRecMarPersonaSP = "SP_GET_RECORD_MARCAS_PERSONAS";
        public string szParamGetRecMarPersonaSP01 = "@N_COD_PERSONA";
        public string szParamGetRecMarPersonaSP02 = "@F_FECHA_INICIAL";
        public string szParamGetRecMarPersonaSP03 = "@F_FECHA_FINAL";

        //informacion de los sp's a ejecutar METODO RegisterPersonalMark
        public string szStIngPersonalSP = "SP_SET_ING_PERSONAL";
        public string szParamStIngPersonalSP01 = "@N_COD_PERSONA";
        public string szParamStIngPersonalSP02 = "@F_FECHA_MARCA";
        public string szParamStIngPersonalSP03 = "@B_FLAG_ING_SAL";
        public string szParamStIngPersonalSP04 = "@C_PTA_ACCESO";

        //informacion de los sp's a ejecutar METODO SearchPersona
        public string szSearchPersonaSP = "SP_BUS_PERSONA";
        public string szParamSearchPersonaSP01 = "@N_COD_PERSONA";
        public string szParamSearchPersonaSP02 = "@C_PRI_NOMBRE";
        public string szParamSearchPersonaSP03 = "@C_SEG_NOMBRE";
        public string szParamSearchPersonaSP04 = "@C_APE_PATERNO";
        public string szParamSearchPersonaSP05 = "@C_APE_MATERNO";
        public string szParamSearchPersonaSP06 = "@C_AREA";

        //informacion de los sp's a ejecutar METODO GetAllConductores
        public string szGetAllConductoresSP = "SP_GET_CONDUCTORES";

        //cantidad de horas de margen para considerar que se olvido de marcar
        public int iQtyHorasToMark = 8;

        CDBAccess pDBAccess = new CDBAccess();

        public void Configure()
        {
            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->Configure");
            try
            {
                //cadena de conexion
                szConnectionString = ConfigurationSettings.AppSettings["szConnectionString"];

                //informacion de los sp's a ejecutar METODO GetPersonalByID
                szGetInfPersonalSP = ConfigurationSettings.AppSettings["szGetInfPersonalSP"];
                szParamGetInfPersonalSP = ConfigurationSettings.AppSettings["szParamGetInfPersonalSP"];

                //informacion de los sp's a ejecutar METODO SearchMarks
                szGetRecMarPersonaSP = ConfigurationSettings.AppSettings["szGetRecMarPersonaSP"];
                szParamGetRecMarPersonaSP01 = ConfigurationSettings.AppSettings["szParamGetRecMarPersonaSP01"];
                szParamGetRecMarPersonaSP02 = ConfigurationSettings.AppSettings["szParamGetRecMarPersonaSP02"];
                szParamGetRecMarPersonaSP03 = ConfigurationSettings.AppSettings["szParamGetRecMarPersonaSP03"];

                //informacion de los sp's a ejecutar METODO RegisterPersonalMark
                szStIngPersonalSP = ConfigurationSettings.AppSettings["szStIngPersonalSP"];
                szParamStIngPersonalSP01 = ConfigurationSettings.AppSettings["szParamStIngPersonalSP01"];
                szParamStIngPersonalSP02 = ConfigurationSettings.AppSettings["szParamStIngPersonalSP02"];
                szParamStIngPersonalSP03 = ConfigurationSettings.AppSettings["szParamStIngPersonalSP03"];
                szParamStIngPersonalSP04 = ConfigurationSettings.AppSettings["szParamStIngPersonalSP04"];

                //informacion de los sp's a ejecutar METODO SearchPersona
                szSearchPersonaSP = ConfigurationSettings.AppSettings["szSearchPersonaSP"];
                szParamSearchPersonaSP01 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP01"];
                szParamSearchPersonaSP02 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP02"];
                szParamSearchPersonaSP03 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP03"];
                szParamSearchPersonaSP04 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP04"];
                szParamSearchPersonaSP05 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP05"];
                szParamSearchPersonaSP06 = ConfigurationSettings.AppSettings["szParamSearchPersonaSP06"];

                //informacion de los sp's a ejecutar METODO GetAllConductores
                szGetAllConductoresSP = ConfigurationSettings.AppSettings["szGetAllConductoresSP"];
            }
            catch (Exception ex)
            {
                //error en configuracion
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->Configure: " + ex.ToString());
            }
        }

        public CPersonal GetPersonalByID(int iCodeToFind)
        {
            CPersonal pPersonal = null;
            Dictionary<string, object> pDictionary = null;
            SqlDataReader pReader = null;
            DateTime dtNow, dtLastOut;
            int iresult;

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->GetPersonalByID:" + iCodeToFind.ToString());

            //consulta a BD
            pDBAccess.szConectionString = this.szConnectionString;
            if (!pDBAccess.Connect())
            {
                return null;
            }

            pDictionary = new Dictionary<string, object>();
            pDictionary.Add(szParamGetInfPersonalSP, iCodeToFind);

            iresult = pDBAccess.LaunchSQLStoreProcedure(szGetInfPersonalSP, pDictionary, out pReader);
            if (iresult != W_OK || pReader == null)
            {
                pDBAccess.Disconnect();
                return null;
            }

            if (!pReader.HasRows)
            {
                pDBAccess.Disconnect();
                return null;
            }

            //match resultset con TOF CPersonal
            pPersonal = new CPersonal();

            //solo 1 fila ya que el PKCODE es unico
            if (!pReader.Read())
            {
                pReader.Close();
                pDBAccess.Disconnect();
                return null;
            }

            try
            {
                pPersonal.iPKCode = iCodeToFind;
                pPersonal.szFirstName = pReader.GetString(0);
                pPersonal.szPatLastName = pReader.GetString(1);
                pPersonal.szMatlastName = pReader.GetString(2);
                pPersonal.szArea = pReader.GetString(3) + " / " + pReader.GetString(10);


                pPersonal.iCondition = pReader.GetInt32(4);     //default no condition

                DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(5), out pPersonal.dtInHour);
                DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(6), out pPersonal.dtTimeInRange);
                DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(7), out pPersonal.dtOutHour);
                DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(8), out pPersonal.dtTimeOutRange);

                //DateTime.TryParse("2012-12-19" + " " + pReader.GetString(5), out pPersonal.dtInHour);
                //DateTime.TryParse("2012-12-19" + " " + pReader.GetString(6), out pPersonal.dtTimeInRange);
                //DateTime.TryParse("2012-12-19" + " " + pReader.GetString(7), out pPersonal.dtOutHour);
                //DateTime.TryParse("2012-12-19" + " " + pReader.GetString(8), out pPersonal.dtTimeOutRange); 

                if (pPersonal.dtInHour > pPersonal.dtOutHour)
                {
                    dtLastOut = pPersonal.dtOutHour;
                    pPersonal.dtOutHour = pPersonal.dtOutHour + (new TimeSpan(1, 0, 0, 0));

                    dtNow = DateTime.Now;
                    if (dtNow >= dtLastOut &&
                        dtNow < pPersonal.dtOutHour)
                    {
                        //tal como esta
                    }
                    else
                    {
                        pPersonal.dtInHour = pPersonal.dtInHour - (new TimeSpan(1, 0, 0, 0));
                        pPersonal.dtOutHour = pPersonal.dtOutHour - (new TimeSpan(1, 0, 0, 0));
                    }

                }

                pPersonal.dtTimeInRange = pPersonal.dtInHour - (new TimeSpan(pPersonal.dtTimeInRange.Hour, pPersonal.dtTimeInRange.Minute, pPersonal.dtTimeInRange.Second));
                pPersonal.dtTimeOutRange = pPersonal.dtOutHour + (new TimeSpan(pPersonal.dtTimeOutRange.Hour, pPersonal.dtTimeOutRange.Minute, pPersonal.dtTimeOutRange.Second));


                pPersonal.szPhotoPath = pReader.GetString(9);
            }
            catch (Exception ex)
            {
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->GetPersonalByID: " + ex.ToString());
                pPersonal = null;
            }

            pReader.Close();
            pDBAccess.Disconnect();
            return pPersonal;
        }

        public List<CPersonal> SearchForPersonal(string szName, string szPatLastName, string szMatLastName, string szArea)
        {
            List<CPersonal> lListPersonal = null;
            Dictionary<string, object> pDictionary = null;
            CPersonal pPersonal = null;
            SqlDataReader pReader = null;
            DateTime dtNow, dtLastOut;
            int iresult;

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->SearchForPersonal:" + szName + "," +
                                                                                szPatLastName + "," +
                                                                                szMatLastName + "," +
                                                                                szArea);
            //consulta a BD
            pDBAccess.szConectionString = this.szConnectionString;
            if (!pDBAccess.Connect())
            {
                return null;
            }

            pDictionary = new Dictionary<string, object>();
            pDictionary.Add(szParamSearchPersonaSP01, 0);
            pDictionary.Add(szParamSearchPersonaSP02, szName);
            pDictionary.Add(szParamSearchPersonaSP03, "");
            pDictionary.Add(szParamSearchPersonaSP04, szPatLastName);
            pDictionary.Add(szParamSearchPersonaSP05, szMatLastName);
            pDictionary.Add(szParamSearchPersonaSP06, szArea);
            //pDictionary.Add("codigoArea",);


            iresult = pDBAccess.LaunchSQLStoreProcedure(szSearchPersonaSP, pDictionary, out pReader);
            if (iresult != W_OK || pReader == null)
            {
                pDBAccess.Disconnect();
                return null;
            }

            if (!pReader.HasRows)
            {
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->SearchForPersonal: No results");
                pDBAccess.Disconnect();
                return null;
            }

            //match resultset con TOF CPersonal
            lListPersonal = new List<CPersonal>();

            while (pReader.Read())
            {
                pPersonal = new CPersonal();
                try
                {
                    pPersonal.iPKCode = pReader.GetInt32(0);
                    pPersonal.szFirstName = pReader.GetString(1);
                    pPersonal.szPatLastName = pReader.GetString(2);
                    pPersonal.szMatlastName = pReader.GetString(3);
                    pPersonal.szArea = pReader.GetString(4);

                    pPersonal.iCondition = pReader.GetInt32(5);     //default no condition
                    pPersonal.codigoArea = pReader.GetInt32(11);
                    pPersonal.codigoSeccion = pReader.GetInt32(12);

                    DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(6), out pPersonal.dtInHour);
                    DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(7), out pPersonal.dtTimeInRange);
                    DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(8), out pPersonal.dtOutHour);
                    DateTime.TryParse(DateTime.Now.ToString("yyyy-MM-dd") + " " + pReader.GetString(9), out pPersonal.dtTimeOutRange);

                    if (pPersonal.dtInHour > pPersonal.dtOutHour)
                    {
                        dtLastOut = pPersonal.dtOutHour;
                        pPersonal.dtOutHour = pPersonal.dtOutHour + (new TimeSpan(1, 0, 0, 0));

                        dtNow = DateTime.Now;
                        if (dtNow >= dtLastOut &&
                            dtNow < pPersonal.dtOutHour)
                        {
                            //tal como esta
                        }
                        else
                        {
                            pPersonal.dtInHour = pPersonal.dtInHour - (new TimeSpan(1, 0, 0, 0));
                            pPersonal.dtOutHour = pPersonal.dtOutHour - (new TimeSpan(1, 0, 0, 0));
                        }

                    }

                    pPersonal.dtTimeInRange = pPersonal.dtInHour - (new TimeSpan(pPersonal.dtTimeInRange.Hour, pPersonal.dtTimeInRange.Minute, pPersonal.dtTimeInRange.Second));
                    pPersonal.dtTimeOutRange = pPersonal.dtOutHour + (new TimeSpan(pPersonal.dtTimeOutRange.Hour, pPersonal.dtTimeOutRange.Minute, pPersonal.dtTimeOutRange.Second));


                    pPersonal.szPhotoPath = pReader.GetString(10);
                }
                catch (Exception ex)
                {
                    CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->SearchForPersonal: " + ex.ToString());
                    pPersonal = null;
                }

                if (pPersonal != null)
                {
                    lListPersonal.Add(pPersonal);
                }
            }

            pReader.Close();
            pDBAccess.Disconnect();

            return lListPersonal;
        }
        public List<CMarkRegister> SearchMarks(CPersonal pPersonal, string szStartDate, string szEndDate)
        {
            if (pPersonal == null)
                return null;

            return SearchMarks(pPersonal.iPKCode, szStartDate, szEndDate);

        }

        public List<CMarkRegister> SearchMarks(int iPKCode, string szStartDate, string szEndDate)
        {
            List<CMarkRegister> lListMarks = null;
            Dictionary<string, object> pDictionary = null;
            SqlDataReader pReader = null;
            CMarkRegister pMark = null;
            int iresult;

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->SearchMarks:" + iPKCode.ToString() + "," +
                                                                          szStartDate + "," +
                                                                          szEndDate);

            pDBAccess.szConectionString = this.szConnectionString;
            if (!pDBAccess.Connect())
            {
                return null;
            }

            pDictionary = new Dictionary<string, object>();
            pDictionary.Add(szParamGetRecMarPersonaSP01, iPKCode);
            pDictionary.Add(szParamGetRecMarPersonaSP02, szStartDate);
            pDictionary.Add(szParamGetRecMarPersonaSP03, szEndDate);


            iresult = pDBAccess.LaunchSQLStoreProcedure(szGetRecMarPersonaSP, pDictionary, out pReader);
            if (iresult != W_OK || pReader == null)
            {
                pDBAccess.Disconnect();
                return null;
            }

            lListMarks = new List<CMarkRegister>();

            if (!pReader.HasRows)
            {
                pDBAccess.Disconnect();
                return lListMarks;
            }

            while (pReader.Read())
            {
                pMark = new CMarkRegister(CMarkRegister.VWISE_DHL_PERSONAL);

                try
                {
                    pMark.iPKCode = iPKCode;
                    DateTime.TryParse(pReader.GetString(1), out pMark.dtInOutTime);
                    pMark.iFlagRegister = pReader.GetInt32(2);
                    pMark.szPuerta = pReader.GetString(3);
                    try 
                    {
                        pMark.nombre = pReader.GetString(4);
                        pMark.area = pReader.GetString(5);
                        pMark.departamento = pReader.GetString(6);
                        pMark.unidad = pReader.GetString(7);
                        pMark.codigoStr = pReader.GetString(8);
                    }
                    catch (Exception exc) 
                    {
                    
                    }
                    

                }
                catch (Exception ex)
                {
                    CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->SearchMarks: " + ex.ToString());
                    pMark = null;
                }

                if (pMark != null)
                {
                    lListMarks.Add(pMark);
                }
            }

            pReader.Close();
            pDBAccess.Disconnect();
            return lListMarks;

        }

        protected int CalculateEventMark(CPersonal pPersonal, DateTime dtRegTime, List<CMarkRegister> lListMarks)
        {
            int iEventMark = W_MARK_INGRESO;
            CMarkRegister pMark = null;
            TimeSpan tsDifference, tsJournal;
            DateTime dtDemiJourne;    //fecha y hora exacta de la mitad de la jornada

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->CalculateEventMark:" + pPersonal.iPKCode.ToString() + "," +
                                                                          dtRegTime.ToString("yyyy/MM/dd HH:mm:ss") + "," +
                                                                          lListMarks.Count.ToString());
            //TODO
            if (lListMarks == null)
            {
                //si hay error al obtener las marcas -> consideramos fail
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->CalculateEventMark lListMarks == null");
                return W_FAIL;
            }

            if (lListMarks.Count == 0)
            {
                //si la persona no registra marcas se considera ingreso
                iEventMark = W_MARK_INGRESO;
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->CalculateEventMark doesn't have previous marks");
                return iEventMark;
            }

            //la lista de marcas de personas para registro solo devuelve la ultima marca
            pMark = lListMarks[0];

            if (pMark == null)
            {
                //si hay error al obtener las marcas -> consideramos excepcional (REVISAR!!)
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->CalculateEventMark Mark == null");
                return W_FAIL;
            }

            tsDifference = dtRegTime - pMark.dtInOutTime;
            if (tsDifference.Seconds < 0)
            {
                //la ultima marca es posterior a esta marca 
                // se puede considerar que hay una falla del sincronizacion de hora
                // pero no se puede concluir que así sea
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->CalculateEventMark marca nueva es anterior a marca pasada");
                return W_FAIL;
            }


            //el ultimo registro esta dentro de la jornada laboral?
            if (pMark.dtInOutTime >= pPersonal.dtTimeInRange &&
               pMark.dtInOutTime <= pPersonal.dtTimeOutRange)
            {
                //si
                //El nuevo registro está dentro de la jornada laboral?
                if (dtRegTime >= pPersonal.dtTimeInRange &&
                    dtRegTime <= pPersonal.dtTimeOutRange)
                {
                    //si
                    switch (pMark.iFlagRegister)
                    {
                        case W_MARK_INGRESO:
                        case W_MARK_EXCEPCIONAL_INGRESO:
                            //ultimo fue ingreso 
                            iEventMark = W_MARK_SALIDA;
                            break;
                        case W_MARK_SALIDA:
                        case W_MARK_EXCEPCIONAL_SALIDA:
                            //ultimo fue salida
                            iEventMark = W_MARK_INGRESO;
                            break;
                    }
                }
                else
                {
                    //no
                    switch (pMark.iFlagRegister)
                    {
                        case W_MARK_INGRESO:
                        case W_MARK_EXCEPCIONAL_INGRESO:
                            //ultimo fue ingreso 
                            iEventMark = W_MARK_EXCEPCIONAL_SALIDA;
                            break;
                        case W_MARK_SALIDA:
                        case W_MARK_EXCEPCIONAL_SALIDA:
                            //ultimo fue salida 
                            iEventMark = W_MARK_EXCEPCIONAL_INGRESO;
                            break;
                    }
                }

            }
            else
            {
                tsJournal = pPersonal.dtOutHour - pPersonal.dtInHour;
                tsJournal = new TimeSpan(tsJournal.Ticks / 2);

                dtDemiJourne = pPersonal.dtInHour + tsJournal;

                //calculamos en que zona de tiempo esta la marca actual
                if (dtRegTime < pPersonal.dtTimeInRange)
                {
                    //antes que inicie la jornada
                    switch (pMark.iFlagRegister)
                    {
                        case W_MARK_INGRESO:
                        case W_MARK_EXCEPCIONAL_INGRESO:
                            //ultimo fue ingreso 
                            iEventMark = W_MARK_EXCEPCIONAL_SALIDA;
                            break;
                        case W_MARK_SALIDA:
                        case W_MARK_EXCEPCIONAL_SALIDA:
                            //ultimo fue salida 
                            iEventMark = W_MARK_EXCEPCIONAL_INGRESO;
                            break;
                    }
                }
                else if (dtRegTime >= pPersonal.dtTimeInRange &&
                         dtRegTime <= dtDemiJourne)
                {
                    //durante la primera mitad
                    iEventMark = W_MARK_INGRESO;

                }
                else if (dtRegTime > dtDemiJourne &&
                         dtRegTime <= pPersonal.dtTimeOutRange)
                {
                    //durante la segunda mitad
                    iEventMark = W_MARK_SALIDA;
                }
                else
                {
                    //despues de jornada
                    iEventMark = W_MARK_EXCEPCIONAL_SALIDA;
                }
            }
            return iEventMark;
        }

        public int RegisterPersonalMark(CPersonal pPersonal, DateTime dtRegTime, string szPuertaAcceso, int idAutoVeh)
        {
            //traer marcas de la persona/vehiculo
            List<CMarkRegister> lListMarks = null;
            Dictionary<string, object> pDictionary = null;
            int iEventmark;
            int iresult;
            int iRowsAffected = 0;

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->RegisterPersonalMark:" + pPersonal.iPKCode.ToString() + "," +
                                                                          dtRegTime.ToString("yyyy/MM/dd HH:mm:ss") + "," +
                                                                          szPuertaAcceso);
            if (pPersonal == null)
            {
                CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->RegisterPersonalMark: pPersonal == null");
                return W_FAIL;
            }


            lListMarks = SearchMarks(pPersonal.iPKCode, "", "");

            if (lListMarks == null)
                return W_FAIL;

            //algoritmo de procesamiento de marcas, determinar si es entrada/salida/excepcional
            iEventmark = CalculateEventMark(pPersonal, dtRegTime, lListMarks);

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_INFO, "CPersonalManager->RegisterPersonalMark: iEventmark = " + iEventmark.ToString());

            if (iEventmark == W_FAIL)
                return W_FAIL;

            //ingresar marca en la BD
            pDBAccess.szConectionString = this.szConnectionString;
            if (!pDBAccess.Connect())
            {
                return W_FAIL;
            }

            pDictionary = new Dictionary<string, object>();
            pDictionary.Add(szParamStIngPersonalSP01, pPersonal.iPKCode);
            pDictionary.Add(szParamStIngPersonalSP02, dtRegTime.ToString("yyyyMMdd HH:mm:ss"));
            pDictionary.Add(szParamStIngPersonalSP03, iEventmark);
            pDictionary.Add(szParamStIngPersonalSP04, szPuertaAcceso);
            pDictionary.Add("@IDVEH", idAutoVeh);

            iresult = pDBAccess.LaunchSQLStoreProcedure(szStIngPersonalSP, pDictionary, out iRowsAffected);
            if (iresult != W_OK)
            {
                pDBAccess.Disconnect();
                return W_FAIL;
            }

            pDBAccess.Disconnect();

            return iEventmark;
        }

        public List<CConductor> GetAllConductor()
        {
            //traer conductores
            List<CConductor> lListConductor = null;
            SqlDataReader pReader = null;
            Dictionary<string, object> pDictionary = new Dictionary<string, object>();
            int iresult;
            CConductor pConductor;

            CLogProcessor.LogInfo(CLogProcessor.W_LOG_DEBUG, "CPersonalManager->GetAllConductor");

            //consulta a BD
            pDBAccess.szConectionString = this.szConnectionString;
            if (!pDBAccess.Connect())
            {
                return null;
            }

            iresult = pDBAccess.LaunchSQLStoreProcedure(szGetAllConductoresSP, pDictionary, out pReader);
            if (iresult != W_OK || pReader == null)
            {
                pDBAccess.Disconnect();
                return null;
            }

            lListConductor = new List<CConductor>();

            if (!pReader.HasRows)
            {
                pDBAccess.Disconnect();
                return lListConductor;
            }

            while (pReader.Read())
            {
                pConductor = new CConductor();

                try
                {
                    pConductor.iPKCode = int.Parse(pReader.GetString(0));
                    pConductor.szConductorDesc = pReader.GetString(1);

                }
                catch (Exception ex)
                {
                    CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR, "CPersonalManager->GetAllConductor: " + ex.ToString());
                    pConductor = null;
                }

                if (pConductor != null)
                {
                    lListConductor.Add(pConductor);
                }
            }

            pReader.Close();
            pDBAccess.Disconnect();
            return lListConductor;
        }
    }
}
